package com.lzjn.code.web.util;

import com.lzjn.code.common.util.FieldUtil;
import com.lzjn.code.web.entity.DataSource;
import com.lzjn.code.web.entity.TableFieldConfig;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;


public class ReadTableUtil {

	private static final Logger logger = LoggerFactory.getLogger(ReadTableUtil.class);

	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private String sql;

	public List<String> readAllTableNames(DataSource dataSource) throws Exception {

		List<String> tableNames = new ArrayList<String>();

		try {

			Class.forName(dataSource.getDiverName());
			this.conn = DriverManager.getConnection(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
		    this.stmt = this.conn.createStatement(1005, 1007);

		    if("mysql".equals(dataSource.getDbType())) {
		    	this.sql = MessageFormat.format("select distinct table_name from information_schema.tables where table_schema = {0}", new Object[] { "'"+dataSource.getDbName()+"'" });
		    }
		    else if("oracle".equals(dataSource.getDbType())) {
		        this.sql = "select distinct c.table_name as table_name from user_tables c";
		    }
		    else if("sqlserver".equals(dataSource.getDbType())) {
		        this.sql = "select distinct c.name as table_name from sys.objects c where type = 'U'";
		    }

		    this.rs = this.stmt.executeQuery(this.sql);
		    while (this.rs.next()) {
		    	String tableName = this.rs.getString(1);
		        tableNames.add(tableName);
		    }

		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			throw e;
		} finally {
			try {
				if (this.stmt != null) {
					this.stmt.close();
					this.stmt = null;
					System.gc();
		        }
		        if (this.conn != null) {
		        	this.conn.close();
		        	this.conn = null;
		        	System.gc();
		        }
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
		    }
		}

		return tableNames;
	}

	public String readTableCommentByName(DataSource dataSource, String tableName) throws Exception {

		String tableComment = null;

		try {

			Class.forName(dataSource.getDiverName());
			this.conn = DriverManager.getConnection(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
			this.stmt = this.conn.createStatement(1005, 1007);

			if("mysql".equals(dataSource.getDbType())) {
				this.sql = MessageFormat.format("select distinct table_name, table_comment from information_schema.tables where table_schema = {0} and table_name = {1}", new Object[] { "'"+dataSource.getDbName()+"'", "'"+tableName+"'" });
			}
			else if("oracle".equals(dataSource.getDbType())) {
				this.sql = MessageFormat.format("select distinct c.table_name as table_name, c.table_name as table_comment from user_tables c where c.table_name = {0}", new Object[] { "'"+tableName+"'" });
			}
			else if("sqlserver".equals(dataSource.getDbType())) {
				this.sql = MessageFormat.format("select distinct c.name as table_name, c.name as table_comment from sys.objects c where type = 'U' and c.name = {0}", new Object[] { "'"+tableName+"'" });
			}

			this.rs = this.stmt.executeQuery(this.sql);
			while (this.rs.next()) {
				tableName = this.rs.getString(1);
				tableComment = this.rs.getString(2);
			}

		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			throw e;
		} finally {
			try {
				if (this.stmt != null) {
					this.stmt.close();
					this.stmt = null;
					System.gc();
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
					System.gc();
				}
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
			}
		}

		return tableComment;
	}

	public List<TableFieldConfig> readTableColumn(DataSource dataSource,String tableName) throws Exception {

		List<TableFieldConfig> fieldList = new ArrayList<TableFieldConfig>();

		try {

			Class.forName(dataSource.getDiverName());
			this.conn = DriverManager.getConnection(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
		    this.stmt = this.conn.createStatement(1005, 1007);

		    if("mysql".equals(dataSource.getDbType())) {
		        this.sql = MessageFormat.format("select column_name,data_type,column_comment,numeric_precision,numeric_scale,character_maximum_length,is_nullable,column_key from information_schema.columns where table_name = {0} and table_schema = {1} order by ordinal_position", new Object[] { "'"+tableName+"'", "'"+dataSource.getDbName()+"'"});
		    }
		    else if("oracle".equals(dataSource.getDbType())) {
		    	this.sql = "select colstable.column_name,colstable.data_type,commentstable.comments,colstable.Data_Precision,colstable.Data_Scale,colstable.Char_Length,colstable.nullable,CASE WHEN EXISTS (SELECT 1 FROM user_constraints con INNER JOIN user_cons_columns col ON con.constraint_name = col.constraint_name AND con.constraint_type = 'P' WHERE col.table_name = colstable.table_name AND col.column_name = colstable.column_name) THEN 'pk' ELSE NULL END pk FROM user_tab_cols colstable  inner join user_col_comments commentstable  on colstable.column_name = commentstable.column_name  where colstable.table_name = commentstable.table_name and colstable.table_name = '" + tableName + "'";
		    }
		    else if("sqlserver".equals(dataSource.getDbType())) {
		        this.sql = "SELECT CAST(col.name AS VARCHAR(50)) column_name,CAST(t.name AS VARCHAR(50)) data_type,CAST(ep.[value] AS NVARCHAR(200)) comment,CAST(COLUMNPROPERTY(col.id, col.name, 'Precision') AS INT) num_precision,CAST(COLUMNPROPERTY(col.id, col.name, 'Scale') AS INT) num_scale,col.length,CASE WHEN col.isnullable = 1 THEN 'y' ELSE 'n' END nullable,CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid  INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = col.id AND sc.colid = col.colid) THEN 'pk' ELSE NULL END pk FROM dbo.syscolumns col LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description' LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description' WHERE obj.name = '"+ tableName +"' ORDER BY col.colorder";
		    }

		    this.rs = this.stmt.executeQuery(this.sql);

		    int orderNo = 1;

		    while(this.rs.next()){

		    	String name = this.rs.getString(1);
		    	String type = this.rs.getString(2);
		    	String description = this.rs.getString(3);
		    	String precision = FieldUtil.getNullString(this.rs.getString(4));
		    	String scale = FieldUtil.getNullString(this.rs.getString(5));
		    	String charMaxLength = FieldUtil.getNullString(this.rs.getString(6));
		    	String nullable = FieldUtil.getNullAble(this.rs.getString(7));
		    	String primaryKey = this.rs.getString(8);

		    	TableFieldConfig field = new TableFieldConfig();
		    	field.setFieldName(name);
		    	field.setFieldType(type);
		    	field.setPropertyName(FieldUtil.formatField(name));
		    	field.setPropertyType(FieldUtil.formatDataType(type, precision, scale));
		    	field.setDescription(StringUtils.isEmpty(description) ? field.getFieldName() : description);
		    	field.setNullable(nullable);
		    	if(StringUtils.isNotEmpty(precision)){
		    		field.setFieldLength(Integer.valueOf(precision));
		    	}
		    	else if(StringUtils.isNotEmpty(charMaxLength)){
					Long charMaxLengthLong = Long.valueOf(charMaxLength);
					if(charMaxLengthLong > 10000) {
						charMaxLength = "10000";
					}
		    		field.setFieldLength(Integer.valueOf(charMaxLength));
		    	}

				if(StringUtils.isNotEmpty(primaryKey) && "PRI".equals(primaryKey)){
					field.setPrimaryKey("Y");
					field.setListShow("N");
					field.setFormShow("N");
				} else {
					field.setPrimaryKey("N");
					field.setListShow("Y");
					field.setFormShow("Y");
				}

				field.setListQuery("N");
				field.setListSort("N");
		    	field.setComponentType("text");
		    	field.setFormValidType("N");
	    		field.setOrderNo(orderNo);

		    	fieldList.add(field);
		    	orderNo++;
		    }

		    if(fieldList.size() == 0){
		    	throw new Exception("该表不存在或者表中没有字段");
		    }
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
			throw e;
		} finally {
			try {
				if (this.stmt != null) {
					this.stmt.close();
					this.stmt = null;
					System.gc();
		        }
		        if (this.conn != null) {
		        	this.conn.close();
		        	this.conn = null;
		        	System.gc();
		        }
			} catch (SQLException e) {
				logger.error(e.getMessage(),e);
		    }
		}

		return fieldList;
	}
}
